package com.ktgj.hotel.utils.jdbc;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

public class test {
    public static void main(String[] args) {
        String url = "jdbc:mysql://172.16.7.51:3306/ktgj?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8";

        JdbcUtil jdbcUtil = new JdbcUtil("mysql", url, "root", "root");

//        String[] str = {"AW阿鲁巴", "AF阿富汗", "AO发哥拉", "Al阿尔巴尼亚", "AD安道尔公国", "AN荷属安的列斯", "AE阿拉伯联合酋长国",
//                "AR阿根廷", "AM亚美尼亚", "AS美属萨摩亚", "AT南极洲", "TF法属南部领土", "AG安提瓜和巴布达", "AU澳大利亚联邦", "AT奥地利",
//                "AZ阿塞拜疆", "BI布隆迪", "BE比利时", "BJ贝宁", "BF布基纳法索", "BD孟加拉人民", "BG保加利亚", "BH巴林国", "BS巴哈马联邦",
//                "BA波斯尼亚和黑塞哥维亚", "BY白俄罗斯", "BZ伯利兹", "BM百慕大群岛", "BO玻利维亚", "BR巴西联邦", "BB巴巴多斯", "BN文莱达鲁萨",
//                "BT不丹王国", "BV布维岛", "BW博茨瓦纳", "CF中非", "CA加拿大", "CC科科斯（基林）群岛", "CH瑞士联邦", "CL智利", "CN中国",
//                "CI科特迪瓦", "CM喀麦隆", "CG刚果", "CK库克群岛", "CO哥伦比亚", "KM科摩罗", "CV佛得角", "CR哥斯达黎加", "TW中国台湾",
//                "CU古巴", "CS圣诞岛", "KY开曼群岛", "CY塞浦路斯", "CZ捷克", "DE德意志联邦", "DJ吉布提", "DM多米尼克联邦", "DK丹麦",
//                "DO多米尼加", "DZ阿尔及利亚", "EC厄瓜多尔", "EG埃及", "ER厄立特里亚国", "EH西撒哈拉", "ES西班牙", "EE爱沙尼亚",
//                "ET埃塞俄比亚", "FI芬兰", "FJ斐济", "FK马尔维纳斯群岛", "FR法兰西", "FO法罗群岛", "FM密克罗尼西亚联邦", "GA加蓬",
//                "GB大不列颠及北爱尔联合王国", "GE格鲁吉亚", "GH加纳", "GI直布罗陀", "GN几内亚", "GP瓜德罗普", "GM冈比亚", "GW几内亚比绍",
//                "GQ赤道几内亚", "GR希腊", "GD格林纳达", "GL格陵兰", "GT危地马拉", "GF法属圭亚那", "GU关岛", "GY圭亚那", "HK香港",
//                "HM赫德岛和麦克唐纳岛", "HN洪都拉斯", "HR克罗地亚", "HT海地", "HU匈牙利", "ID印度尼西亚", "IN印度", "IO英属印度洋领土",
//                "IE爱尔兰", "IR伊朗", "IQ伊拉克", "IS冰岛", "IL以色列", "IT意大利", "JM牙买加", "JO约旦", "JP日本", "KZ哈萨克斯坦",
//                "KE肯尼亚", "KG吉尔吉斯", "KH柬埔寨", "KI基里巴斯", "KN圣基茨和尼维斯", "KR大韩发国", "KW科威特国", "LA老挝", "LB黎巴嫩",
//                "LR利比里亚", "LY大阿拉伯利比亚", "LC圣卢西亚", "LI列支敦士登", "LK斯里兰卡", "CK库克群岛", "CO哥伦比亚", "KM科摩罗", "CV佛得角", "CR哥斯达黎加", "TW中国台湾",
//                "CU古巴", "CS圣诞岛", "KY开曼群岛", "CY塞浦路斯", "CZ捷克", "DE德意志联邦", "DJ吉布提", "DM多米尼克联邦", "DK丹麦",
//                "DO多米尼加", "DZ阿尔及利亚", "EC厄瓜多尔", "EG埃及", "ER厄立特里亚国", "EH西撒哈拉", "ES西班牙", "EE爱沙尼亚", "ET埃塞俄比亚", "FI芬兰", "FJ斐济", "FK马尔维纳斯群岛", "FR法兰西", "FO法罗群岛", "FM密克罗尼西亚联邦", "GA加蓬",
//                "GB大不列颠及北爱尔联合王国", "GE格鲁吉亚", "GH加纳", "GI直布罗陀", "GN几内亚"};

        //查询酒店来源表筛选出重复数据
        List<Map<String, Object>> lstData = jdbcUtil.executeQuerySql("SELECT hotel_code FROM hotel_supplier_link GROUP BY hotel_code HAVING count(*) > 1");

        List<Map<String, Object>> nameList = new ArrayList<>();

        for (Map<String, Object> lstDatum : lstData) {
            //获取酒店艺龙id
            String hotelCode = lstDatum.get("hotel_code").toString();
            //根据艺龙id查询酒店来源表，查询重复数据
            List<Map<String, Object>> supplierLink = jdbcUtil.executeQuerySql("select * from hotel_supplier_link where hotel_code = " + hotelCode);
            //数据数量大于一条
            if (supplierLink.size() > 1){
                for (Map<String, Object> stringObjectMap : supplierLink) {
                    //获取数据库酒店id
                    Long id = Long.valueOf(stringObjectMap.get("hotel_id").toString());
                    //获取酒店来源表主键
                    Long hotelId = Long.valueOf(stringObjectMap.get("id").toString());
                    List<Map<String, Object>> hotel = jdbcUtil.executeQuerySql("select * from hotel where id = " + id);
                    //酒店表没有id为 “id” 数据，则删除来源表相关数据
                    if (hotel.size() <= 0){
                        jdbcUtil.execute("delete from hotel where id = " + id);
                        System.out.println(hotelId + "已删除");
                    }
                    else {//酒店表有数据，将数据加入nameList
                        nameList.add(stringObjectMap);
                    }
                }

                List<Long> time = new ArrayList<>();
                SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                //nameList中数据数量大于1，进行进一步筛选
                if (nameList.size() > 1){
                    for (Map<String, Object> stringObjectMap : nameList) {
                        Date date = null;
                        try {
                            date = format.parse(stringObjectMap.get("create_time").toString());
                        } catch (Exception e){
                            System.out.println("日期错误");
                        }

                        stringObjectMap.put("time", date.getTime());
                        time.add(date.getTime());
                    }

                    Long dateTime = null;
                    if (time.size() == 2){
                        if (time.get(0) > time.get(1)){
                            dateTime = time.get(0);
                        }
                        else {
                            dateTime = time.get(1);
                        }
                    }
                    else if (time.size() == 3){
                        if (time.get(0) > time.get(1)){
                            dateTime = time.get(0);
                        }
                        else {
                            dateTime = time.get(1);
                        }

                        if (dateTime < time.get(2)){
                            dateTime = time.get(2);
                        }
                    }

                    try {
                        for (Map<String, Object> stringObjectMap : nameList) {
                            Long page = Long.valueOf(stringObjectMap.get("time").toString());
                            Long id = Long.valueOf(stringObjectMap.get("id").toString());
                            if (dateTime != page){
                                jdbcUtil.execute("update hotel_supplier_link set is_valid = 0 where id = " + id);
                                System.out.println(id + "已禁用");
                            }
                            else {
                                jdbcUtil.execute("update hotel_supplier_link set is_valid = 1 where id = " + id);
                                System.out.println(id + "已启用");
                            }
                        }
                    } catch (Exception e){
                        System.out.println(hotelCode + "出现异常");
                    }
                }
            }
//            Iterator<String> iterator = lstDatum.keySet().iterator();
//            Object name = null;
//            while (iterator.hasNext()){
//                String key = iterator.next();
//                if (key.equals("CountryName")){
//                    name = lstDatum.get(key);
//                    nameList.add(name.toString());
//                }
//            }
        }
        System.out.println("结束");
        jdbcUtil.close();
//        List<String> stringList = new ArrayList<>();

//        for (String s : nameList) {
//            List<Map<String, Object>> mapList = jdbcUtil.executeQuerySql("SELECT * FROM nation WHERE NATION_NAME = " + s);
//            if (mapList == null || mapList.size() <= 0){
//                System.out.println(s);
//                stringList.add(s);
//            }
//        }
//        System.out.println(stringList.size());

//        if (lstDatum!=null && lstDatum.size()>0){
//            jdbcUtil.execute("UPDATE hotel SET county_name = (SELECT county_name FROM dic_county WHERE id = " + countyId+") WHERE id = "+id);
//        }



//        List<Map<String, Object>> lstData = jdbcUtil.executeQuerySql("SELECT * FROM nation");
//
//        for (Map<String, Object> lstDatum : lstData) {
//            if (lstDatum!=null && lstDatum.size()>0){
//                jdbcUtil.execute("UPDATE hotel SET county_name = (SELECT county_name FROM dic_county WHERE id = " + countyId+") WHERE id = "+id);
//            }
//        }

        jdbcUtil.close();
    }
}
